#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class StaffInfoProvider : CY.GFive.Core.Providers.DALProvider.IStaffInfoProvider
	{
		#region Sql
		private static readonly string SqlSelectStaffInfo = "SELECT [Id], [Code],[Name],[Gender],[Nation],[Origin],[IdentifyCode],[Birthday],[PoliticalCode],[JoinDate],[EducateLvl],[ScholarLvl],[TitleLvl],[QualityLvl],[TeachSubject],[OriginWork],[GraduteSchool],[ProfessName],[SchoolTypeCode],[Address],[CellPhone],[Phone],[StaffCate],[Photo],[DepartmentCode],[Des],[AttachmentCode],[Positon] FROM [StaffInfo] WHERE  [Id]=@Id";

		private static readonly string SqlInsertStaffInfo = "INSERT INTO [StaffInfo]([Code],[Name],[Gender],[Nation],[Origin],[IdentifyCode],[Birthday],[PoliticalCode],[JoinDate],[EducateLvl],[ScholarLvl],[TitleLvl],[QualityLvl],[TeachSubject],[OriginWork],[GraduteSchool],[ProfessName],[SchoolTypeCode],[Address],[CellPhone],[Phone],[StaffCate],[Photo],[DepartmentCode],[Des],[AttachmentCode],[Positon]) values(@Code,@Name,@Gender,@Nation,@Origin,@IdentifyCode,@Birthday,@PoliticalCode,@JoinDate,@EducateLvl,@ScholarLvl,@TitleLvl,@QualityLvl,@TeachSubject,@OriginWork,@GraduteSchool,@ProfessName,@SchoolTypeCode,@Address,@CellPhone,@Phone,@StaffCate,@Photo,@DepartmentCode,@Des,@AttachmentCode,@Positon);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateStaffInfo = "UPDATE [StaffInfo] SET [Code] = @Code,[Name] = @Name,[Gender] = @Gender,[Nation] = @Nation,[Origin] = @Origin,[IdentifyCode] = @IdentifyCode,[Birthday] = @Birthday,[PoliticalCode] = @PoliticalCode,[JoinDate] = @JoinDate,[EducateLvl] = @EducateLvl,[ScholarLvl] = @ScholarLvl,[TitleLvl] = @TitleLvl,[QualityLvl] = @QualityLvl,[TeachSubject] = @TeachSubject,[OriginWork] = @OriginWork,[GraduteSchool] = @GraduteSchool,[ProfessName] = @ProfessName,[SchoolTypeCode] = @SchoolTypeCode,[Address] = @Address,[CellPhone] = @CellPhone,[Phone] = @Phone,[StaffCate] = @StaffCate,[Photo] = @Photo,[DepartmentCode] = @DepartmentCode,[Des] = @Des,[AttachmentCode] = @AttachmentCode,[Positon] = @Positon WHERE  [Id]=@Id";

		private static readonly string SqlDeleteStaffInfo = "DELETE [StaffInfo] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllStaffInfo = "SELECT [Id], [Code],[Name],[Gender],[Nation],[Origin],[IdentifyCode],[Birthday],[PoliticalCode],[JoinDate],[EducateLvl],[ScholarLvl],[TitleLvl],[QualityLvl],[TeachSubject],[OriginWork],[GraduteSchool],[ProfessName],[SchoolTypeCode],[Address],[CellPhone],[Phone],[StaffCate],[Photo],[DepartmentCode],[Des],[AttachmentCode],[Positon] FROM [StaffInfo]";
		#endregion

		#region IStaffInfoProvider Members
		public Core.Business.StaffInfo Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectStaffInfo);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.StaffInfo staffInfo = new Core.Business.StaffInfo();

				if (!reader.IsDBNull(0)) staffInfo.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) staffInfo.Code = reader.GetString(1);
				if (!reader.IsDBNull(2)) staffInfo.Name = reader.GetString(2);
				if (!reader.IsDBNull(3)) staffInfo.Gender = reader.GetString(3);
				if (!reader.IsDBNull(4)) staffInfo.Nation = reader.GetString(4);
				if (!reader.IsDBNull(5)) staffInfo.Origin = reader.GetString(5);
				if (!reader.IsDBNull(6)) staffInfo.IdentifyCode = reader.GetString(6);
				if (!reader.IsDBNull(7)) staffInfo.Birthday = reader.GetDateTime(7);
				if (!reader.IsDBNull(8)) staffInfo.PoliticalCode = reader.GetString(8);
				if (!reader.IsDBNull(9)) staffInfo.JoinDate = reader.GetDateTime(9);
				if (!reader.IsDBNull(10)) staffInfo.EducateLvl = reader.GetString(10);
				if (!reader.IsDBNull(11)) staffInfo.ScholarLvl = reader.GetString(11);
				if (!reader.IsDBNull(12)) staffInfo.TitleLvl = reader.GetString(12);
				if (!reader.IsDBNull(13)) staffInfo.QualityLvl = reader.GetString(13);
				if (!reader.IsDBNull(14)) staffInfo.TeachSubject = reader.GetString(14);
				if (!reader.IsDBNull(15)) staffInfo.OriginWork = reader.GetString(15);
				if (!reader.IsDBNull(16)) staffInfo.GraduteSchool = reader.GetString(16);
				if (!reader.IsDBNull(17)) staffInfo.ProfessName = reader.GetString(17);
				if (!reader.IsDBNull(18)) staffInfo.SchoolTypeCode = reader.GetString(18);
				if (!reader.IsDBNull(19)) staffInfo.Address = reader.GetString(19);
				if (!reader.IsDBNull(20)) staffInfo.CellPhone = reader.GetString(20);
				if (!reader.IsDBNull(21)) staffInfo.Phone = reader.GetString(21);
				if (!reader.IsDBNull(22)) staffInfo.StaffCate = reader.GetString(22);
				if (!reader.IsDBNull(23)) staffInfo.Photo = reader.GetString(23);
				if (!reader.IsDBNull(24)) staffInfo.DepartmentCode = reader.GetString(24);
				if (!reader.IsDBNull(25)) staffInfo.Des = reader.GetString(25);
				if (!reader.IsDBNull(26)) staffInfo.AttachmentCode = reader.GetString(26);
				if (!reader.IsDBNull(27)) staffInfo.Positon = reader.GetString(27);

				return staffInfo;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.StaffInfo staffInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Code", SqlDbType.NVarChar, staffInfo.Code);
			sql.AddParameter("@Name", SqlDbType.NVarChar, staffInfo.Name);
			sql.AddParameter("@Gender", SqlDbType.NVarChar, staffInfo.Gender);
			sql.AddParameter("@Nation", SqlDbType.NVarChar, staffInfo.Nation);
			sql.AddParameter("@Origin", SqlDbType.NVarChar, staffInfo.Origin);
			sql.AddParameter("@IdentifyCode", SqlDbType.NVarChar, staffInfo.IdentifyCode);
			sql.AddParameter("@Birthday", SqlDbType.DateTime, staffInfo.Birthday);
			sql.AddParameter("@PoliticalCode", SqlDbType.NVarChar, staffInfo.PoliticalCode);
			sql.AddParameter("@JoinDate", SqlDbType.DateTime, staffInfo.JoinDate);
			sql.AddParameter("@EducateLvl", SqlDbType.NVarChar, staffInfo.EducateLvl);
			sql.AddParameter("@ScholarLvl", SqlDbType.NVarChar, staffInfo.ScholarLvl);
			sql.AddParameter("@TitleLvl", SqlDbType.NVarChar, staffInfo.TitleLvl);
			sql.AddParameter("@QualityLvl", SqlDbType.NVarChar, staffInfo.QualityLvl);
			sql.AddParameter("@TeachSubject", SqlDbType.NVarChar, staffInfo.TeachSubject);
			sql.AddParameter("@OriginWork", SqlDbType.NVarChar, staffInfo.OriginWork);
			sql.AddParameter("@GraduteSchool", SqlDbType.NVarChar, staffInfo.GraduteSchool);
			sql.AddParameter("@ProfessName", SqlDbType.NVarChar, staffInfo.ProfessName);
			sql.AddParameter("@SchoolTypeCode", SqlDbType.NVarChar, staffInfo.SchoolTypeCode);
			sql.AddParameter("@Address", SqlDbType.NVarChar, staffInfo.Address);
			sql.AddParameter("@CellPhone", SqlDbType.NVarChar, staffInfo.CellPhone);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, staffInfo.Phone);
			sql.AddParameter("@StaffCate", SqlDbType.NVarChar, staffInfo.StaffCate);
			sql.AddParameter("@Photo", SqlDbType.NVarChar, staffInfo.Photo);
			sql.AddParameter("@DepartmentCode", SqlDbType.NVarChar, staffInfo.DepartmentCode);
			sql.AddParameter("@Des", SqlDbType.NVarChar, staffInfo.Des);
			sql.AddParameter("@AttachmentCode", SqlDbType.NVarChar, staffInfo.AttachmentCode);
			sql.AddParameter("@Positon", SqlDbType.NVarChar, staffInfo.Positon);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertStaffInfo);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) staffInfo.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.StaffInfo staffInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Code", SqlDbType.NVarChar, staffInfo.Code);
			sql.AddParameter("@Name", SqlDbType.NVarChar, staffInfo.Name);
			sql.AddParameter("@Gender", SqlDbType.NVarChar, staffInfo.Gender);
			sql.AddParameter("@Nation", SqlDbType.NVarChar, staffInfo.Nation);
			sql.AddParameter("@Origin", SqlDbType.NVarChar, staffInfo.Origin);
			sql.AddParameter("@IdentifyCode", SqlDbType.NVarChar, staffInfo.IdentifyCode);
			sql.AddParameter("@Birthday", SqlDbType.DateTime, staffInfo.Birthday);
			sql.AddParameter("@PoliticalCode", SqlDbType.NVarChar, staffInfo.PoliticalCode);
			sql.AddParameter("@JoinDate", SqlDbType.DateTime, staffInfo.JoinDate);
			sql.AddParameter("@EducateLvl", SqlDbType.NVarChar, staffInfo.EducateLvl);
			sql.AddParameter("@ScholarLvl", SqlDbType.NVarChar, staffInfo.ScholarLvl);
			sql.AddParameter("@TitleLvl", SqlDbType.NVarChar, staffInfo.TitleLvl);
			sql.AddParameter("@QualityLvl", SqlDbType.NVarChar, staffInfo.QualityLvl);
			sql.AddParameter("@TeachSubject", SqlDbType.NVarChar, staffInfo.TeachSubject);
			sql.AddParameter("@OriginWork", SqlDbType.NVarChar, staffInfo.OriginWork);
			sql.AddParameter("@GraduteSchool", SqlDbType.NVarChar, staffInfo.GraduteSchool);
			sql.AddParameter("@ProfessName", SqlDbType.NVarChar, staffInfo.ProfessName);
			sql.AddParameter("@SchoolTypeCode", SqlDbType.NVarChar, staffInfo.SchoolTypeCode);
			sql.AddParameter("@Address", SqlDbType.NVarChar, staffInfo.Address);
			sql.AddParameter("@CellPhone", SqlDbType.NVarChar, staffInfo.CellPhone);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, staffInfo.Phone);
			sql.AddParameter("@StaffCate", SqlDbType.NVarChar, staffInfo.StaffCate);
			sql.AddParameter("@Photo", SqlDbType.NVarChar, staffInfo.Photo);
			sql.AddParameter("@DepartmentCode", SqlDbType.NVarChar, staffInfo.DepartmentCode);
			sql.AddParameter("@Des", SqlDbType.NVarChar, staffInfo.Des);
			sql.AddParameter("@AttachmentCode", SqlDbType.NVarChar, staffInfo.AttachmentCode);
			sql.AddParameter("@Positon", SqlDbType.NVarChar, staffInfo.Positon);
			sql.AddParameter("@Id", SqlDbType.Int, staffInfo.Id);
			sql.ExecuteSql(SqlUpdateStaffInfo);
		}

		public void Delete(Core.Business.StaffInfo staffInfo)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, staffInfo.Id);
			sql.ExecuteSql(SqlDeleteStaffInfo);
		}

		public IList<Core.Business.StaffInfo> GetAllStaffInfo()
		{
			IList<Core.Business.StaffInfo> staffInfolist = new List<Core.Business.StaffInfo>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllStaffInfo);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.StaffInfo staffInfo = new Core.Business.StaffInfo();

					if (!reader.IsDBNull(0)) staffInfo.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) staffInfo.Code = reader.GetString(1);
					if (!reader.IsDBNull(2)) staffInfo.Name = reader.GetString(2);
					if (!reader.IsDBNull(3)) staffInfo.Gender = reader.GetString(3);
					if (!reader.IsDBNull(4)) staffInfo.Nation = reader.GetString(4);
					if (!reader.IsDBNull(5)) staffInfo.Origin = reader.GetString(5);
					if (!reader.IsDBNull(6)) staffInfo.IdentifyCode = reader.GetString(6);
					if (!reader.IsDBNull(7)) staffInfo.Birthday = reader.GetDateTime(7);
					if (!reader.IsDBNull(8)) staffInfo.PoliticalCode = reader.GetString(8);
					if (!reader.IsDBNull(9)) staffInfo.JoinDate = reader.GetDateTime(9);
					if (!reader.IsDBNull(10)) staffInfo.EducateLvl = reader.GetString(10);
					if (!reader.IsDBNull(11)) staffInfo.ScholarLvl = reader.GetString(11);
					if (!reader.IsDBNull(12)) staffInfo.TitleLvl = reader.GetString(12);
					if (!reader.IsDBNull(13)) staffInfo.QualityLvl = reader.GetString(13);
					if (!reader.IsDBNull(14)) staffInfo.TeachSubject = reader.GetString(14);
					if (!reader.IsDBNull(15)) staffInfo.OriginWork = reader.GetString(15);
					if (!reader.IsDBNull(16)) staffInfo.GraduteSchool = reader.GetString(16);
					if (!reader.IsDBNull(17)) staffInfo.ProfessName = reader.GetString(17);
					if (!reader.IsDBNull(18)) staffInfo.SchoolTypeCode = reader.GetString(18);
					if (!reader.IsDBNull(19)) staffInfo.Address = reader.GetString(19);
					if (!reader.IsDBNull(20)) staffInfo.CellPhone = reader.GetString(20);
					if (!reader.IsDBNull(21)) staffInfo.Phone = reader.GetString(21);
					if (!reader.IsDBNull(22)) staffInfo.StaffCate = reader.GetString(22);
					if (!reader.IsDBNull(23)) staffInfo.Photo = reader.GetString(23);
					if (!reader.IsDBNull(24)) staffInfo.DepartmentCode = reader.GetString(24);
					if (!reader.IsDBNull(25)) staffInfo.Des = reader.GetString(25);
					if (!reader.IsDBNull(26)) staffInfo.AttachmentCode = reader.GetString(26);
					if (!reader.IsDBNull(27)) staffInfo.Positon = reader.GetString(27);

					staffInfo.MarkOld();
					staffInfolist.Add(staffInfo);
				}
				reader.Close();
			}
			return staffInfolist;
		}
		#endregion
	}
}
